package day.statistic;

import entity.day.TripLink;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utility.database.SqlServerAccess;
import utility.time.ProcessTime;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * Created by lit on 2018/4/13.
 */
public class ProcessStatTripLink {
    //数据连接
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;

    private Logger logger = null;

    private boolean initMSSQL(){
        if( connection == null ){
            try {
                connection = SqlServerAccess.getInstance();
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    private void finishMSSQL(){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void processTripLink(String[] dayStatTime){
        //String latestTime = getLatestTime();
        logger = LoggerFactory.getLogger(ProcessStatTripLink.class);

        clearStatDayTripLink(dayStatTime);

        ArrayList<TripLink> tripLinksArray = getTripLink(dayStatTime);
        addStatDayTripLink(tripLinksArray);
        finishMSSQL();
    }

    private void clearStatDayTripLink(String[] dayStatTime) {
        if(initMSSQL()){
            String sql = "DELETE FROM stat_day_tripLink where TravelDate = ?";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,dayStatTime[0]);
                int num = preparedStatement.executeUpdate();
                connection.commit();
                if(num > 0){
                    logger.info("清除{}重复数据{}条",dayStatTime[0],num);
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 3.数据插入数据库
     * @param tripLinksArray
     */
    private void addStatDayTripLink(ArrayList<TripLink> tripLinksArray) {
        if(initMSSQL()){
            String sql = "insert into stat_day_tripLink (LinkID,TravelDate,Whole,AmPeak,PmPeak) values (?,?,?,?,?)";
            try{
                int num = 0;
                preparedStatement = connection.prepareStatement(sql);
                for(int i=0; i < tripLinksArray.size(); i++){
                    TripLink tripLink = tripLinksArray.get(i);
                    preparedStatement.setLong(1,tripLink.getLinkID());
                    preparedStatement.setString(2,tripLink.getTravelDate());
                    preparedStatement.setLong(3,tripLink.getWhole());
                    preparedStatement.setInt(4,tripLink.getAmPeak());
                    preparedStatement.setInt(5,tripLink.getPmPeak());
                    preparedStatement.addBatch();
                    if((i+1)%100 == 0){
                        preparedStatement.executeBatch();
                    }
                    num++;
                }
                preparedStatement.executeBatch();
                connection.commit();
                logger.info("行程link插入数据{}条",num);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     *
     * @param dayStatTime
     * @return
     */
    private ArrayList<TripLink> getTripLink(String[] dayStatTime) {
        if(initMSSQL()){
            ArrayList<TripLink> tripLinksArray = new ArrayList<>();
            String sql = "SELECT tabl1.LinkID,tabl1.TravelDate,Whole,AmPeak,PmPeak FROM";
            //Whole
            sql +=  "(SELECT LinkID,CONVERT (VARCHAR(10), StatDate, 20) AS TravelDate,SUM (UseIntensity) AS Whole FROM rtm_tripLink" +
                    " WHERE StatDate >= ? AND StatDate < ? " +
                    " GROUP BY LinkID,CONVERT (VARCHAR(10), StatDate, 20)) as tabl1";
            //AmPeak
            sql +=  " LEFT JOIN " +
                    "  (SELECT LinkID,CONVERT (VARCHAR(10), StatDate, 20) AS TravelDate,SUM (UseIntensity) AS PmPeak FROM rtm_tripLink " +
                    "    WHERE StatHour >= 7 AND StatHour < 9 AND StatDate >= ? AND StatDate < ?" +
                    "    GROUP BY LinkID,CONVERT (VARCHAR(10), StatDate, 20) " +
                    "  ) tabl2 ON tabl1.LinkId = tabl2.LInkId AND tabl1.TravelDate = tabl2.TravelDate";
            //PmPeak
            sql +=  " LEFT JOIN " +
                    " (SELECT LinkID,CONVERT (VARCHAR(10), StatDate, 20) AS TravelDate,SUM (UseIntensity) AS AmPeak FROM rtm_tripLink" +
                    " WHERE StatHour >= 7 AND StatHour < 9 AND StatDate >= ? AND StatDate < ?" +
                    " GROUP BY LinkID,CONVERT (VARCHAR(10), StatDate, 20) " +
                    " ) tabl3 ON tabl1.LinkId = tabl3.LInkId AND tabl1.TravelDate = tabl3.TravelDate";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,dayStatTime[0]);
                preparedStatement.setString(2,dayStatTime[1]);
                preparedStatement.setString(3,dayStatTime[0]);
                preparedStatement.setString(4,dayStatTime[1]);
                preparedStatement.setString(5,dayStatTime[0]);
                preparedStatement.setString(6,dayStatTime[1]);
                ResultSet rs = preparedStatement.executeQuery();
                while(rs.next()){
                    TripLink tripLink = new TripLink();
                    tripLink.setLinkID(rs.getLong("LinkID"));
                    tripLink.setTravelDate(rs.getString("TravelDate"));
                    tripLink.setWhole(rs.getLong("Whole"));
                    tripLink.setAmPeak(rs.getInt("AmPeak"));
                    tripLink.setPmPeak(rs.getInt("PmPeak"));
                    tripLinksArray.add(tripLink);
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            return tripLinksArray;
        }
        return null;
    }

    /**
     * 获取需要处理的日统计日期
     * @return
     */
    private String getLatestTime() {
        if(initMSSQL()){

            String sql = "SELECT TOP 1 DATEADD(DAY,1,TravelDate) as latestTime FROM stat_day_tripLink ORDER BY TravelDate DESC";
            try{
                preparedStatement = connection.prepareStatement(sql);
                ResultSet rs = preparedStatement.executeQuery();
                if(rs.next()){
                    return rs.getString("latestTime");
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }
}
